﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using System.Data.SqlClient;
using SQLServerDAL;
using Model;


namespace DAL
{
  public class MenbersDal : IMenbersDAL
  {
    string strSql = null;
    public int AddMenbers(Model.Menbers menber)
    {
      strSql = "Insert into Menbers Values(@QQ,@Name,@IdentityCardAddress,@IdentityCardFront,@IdentityCardBack,@ZhifubaoAccountName,@Photo)";
      SqlParameter[] arr = new SqlParameter[]
            {
                new SqlParameter( "@QQ",menber.QQ),
                new SqlParameter( "@Name",menber.Name),
                new SqlParameter( "@IdentityCardAddress",menber.IdentityCardAddress),
                new SqlParameter( "@IdentityCardFront",menber.IdentityCardFront),
                new SqlParameter( "@IdentityCardBack",menber.IdentityCardBack),
                new SqlParameter( "@ZhifubaoAccountName",menber.ZhifubaoAccountName),
                new SqlParameter( "@Photo",menber.Photo),
            
            };
      return SQLHelper.ExecuteSql(strSql, arr);
    }

    public int DeleteMenbersr(string menberId)
    {
      strSql = "delete Menbers where MenberID=@MenberID";
      SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@MenberID",menberId)
            };
      return SQLHelper.ExecuteSql(strSql, paras);
    }

    public int UpdateMenbers(Model.Menbers menber)
    {
      string strSQL = "update Menbers set QQ=@QQ,Name=@Name,IdentityCardAddress=@IdentityCardAddress,IdentityCardFront=@IdentityCardFront,IdentityCardBack=@IdentityCardBack,ZhifubaoAccountName=@ZhifubaoAccountName,Photo=@Photo where MenberID=@MenberID";
      SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@QQ",menber.QQ),
                new SqlParameter("@Name",menber.Name),
                new SqlParameter("@IdentityCardAddress",menber.IdentityCardAddress),
                new SqlParameter("@IdentityCardFront",menber.IdentityCardFront),
                new SqlParameter("@IdentityCardBack",menber.IdentityCardBack),
                new SqlParameter("@ZhifubaoAccountName",menber.ZhifubaoAccountName),
                new SqlParameter("@Photo",menber.Photo),
                new SqlParameter("@MenberID",menber.MenberID)
            };
      return SQLHelper.ExecuteSql(strSQL, paras);
    }
    public int GetMenbers(int MenberID)
    {
      strSql = "select * from where MenberID=@MenberID";
      throw new NotImplementedException();
    }
    public int GetMenbers(string qq, string name)
    {
      strSql = "select * from Menbers where QQ=@QQ and Name=@Name";
      SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@QQ",qq),
                new SqlParameter("@Name",name)
            };


      return SQLHelper.ExecuteScalar(strSql, paras);
    }
    public Menbers GetListMenbers(string menberID)
    {
      strSql = "select * from Menbers where MenberID=@MenberID";
      SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@MenberID",menberID)
            };

      Menbers myMenber = new Menbers();
      SqlDataReader reader = SQLHelper.ExecuteReader(strSql, paras);
      while (reader.Read())
      {
        myMenber.IdentityCardFront = reader["IdentityCardFront"].ToString();
        myMenber.IdentityCardBack = reader["IdentityCardBack"].ToString();
        myMenber.Photo = reader["Photo"].ToString();
      }
      return myMenber;
    }
    public System.Data.DataTable GetAllMenbers()
    {
      strSql = "select * from Menbers";
      return SQLHelper.ExecuteDt(strSql);
    }



    public System.Data.DataTable GetAllMenbers(string size, string index, string name, string order)
    {
      int totalSize = Convert.ToInt32(size) * Convert.ToInt32(index);
      strSql = @"select top " + size + " * from Menbers where MenberID not in (select top " + totalSize.ToString() + " MenberID from Menbers order by " + name + " " + order + ")";
      return SQLHelper.ExecuteDt(strSql);
    }
  }
}
